package com.flymobi.util;

public class QL {
	public static final class HQL {

		public static final class Users {
			public static final String FIND_BY_NAMEEMAILMOBILE = "from Users m WHERE m.name=? or m.mobile=? or m.email=?";
			public static final String FIND_BY_Name = "from Users m WHERE m.name=?";
			public static final String LIST_BY_KEY = "from Users m WHERE m.mobile like ? or m.email like ?";
			public static final String GET_COUNT_KEY = "SELECT COUNT(*) FROM Users m  WHERE m.mobile like ? or m.email like ?";
			public static final String EXP = "*********";
		}

		public static class Manager {
			public static final String LIST = "from Manager m ORDER BY m.id DESC";
			public static final String FIND_BY_NAME = "from Manager m where m.name=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Manager m";
			public static final String LIST_KEY = "from Manager m where m.name like ? ORDER BY m.id DESC";
			public static final String GET_COUNT_KEY = "SELECT COUNT(*) FROM Manager m where m.name like ?";
		}

		public static final class SysPower {
			public static final String LIST = "FROM SysPower m WHERE m.rolesId=?";
			public static final String DELETE_BY_MENUID = "DELETE FROM SysPower m WHERE m.sysMenuId=?";
			public static final String DELETE_BY_RolesID = "DELETE FROM SysPower m WHERE m.rolesId=?";
		}

		public static final class SysMenu {
			public static final String LIST = "FROM SysMenu m WHERE m.fatherId=? order by m.idx";
			public static String LIST_BY_POWER = "FROM SysMenu m WHERE m.fatherId=? and m.id in(SELECT s.sysMenuId FROM SysPower s WHERE s.rolesId=?) order by m.idx";
		}

		public static final class Roles {

			public static final String LIST = "from Roles m ORDER BY m.id DESC";

		}

		public static final class Comment {
			public static final String LIST = "from Comment m ORDER BY m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Comment m";
			public static final String LIST_REPLY = "from Comment m where m.commentId=? ORDER BY m.id DESC";
			public static final String LIST_BY_NEWS = "from Comment m where m.newsId=? and ((m.shenhe=1 and m.shenheType=0) or (m.shenheType=1 and m.shenhe!=2))  and m.type=1 ORDER BY m.id DESC";
			public static final String LIST_BY_ZHUANTI = "from Comment m where m.newsId=? and ((m.shenhe=1 and m.shenheType=0) or (m.shenheType=1 and m.shenhe!=2))  and m.type=2 ORDER BY m.id DESC";
			public static final String LIST_BY_NEWS2 = "from Comment m where m.newsId=? and m.type=1 ORDER BY m.id DESC";
			public static final String LIST_BY_ZHUANTI2 = "from Comment m where m.newsId=? and m.type=2 ORDER BY m.id DESC";
			public static final String GET_COUNT_BY_NEWS = "SELECT COUNT(*) FROM Comment m where m.newsId=? and ((m.shenhe=1 and m.shenheType=0) or (m.shenheType=1 and m.shenhe!=2)) and m.type=1";
			public static final String GET_COUNT_BY_ZHUANTI = "SELECT COUNT(*) FROM Comment m where m.newsId=? and ((m.shenhe=1 and m.shenheType=0) or (m.shenheType=1 and m.shenhe!=2)) and m.type=2";
			public static final String GET_COUNT_BY_NEWS2 = "SELECT COUNT(*) FROM Comment m where m.newsId=? and m.type=1";
			public static final String GET_COUNT_BY_ZHUANTI2 = "SELECT COUNT(*) FROM Comment m where m.newsId=? and m.type=2";
			public static final String SEARCH = "from Comment m where (m.newsId in(select n.id from News n where n.title like ?) or m.type=2) and m.shenhe=? ORDER BY m.id DESC";
			public static final String GET_COUNT_SEARCH =  "select count(*) from Comment m where (m.newsId in(select n.id from News n where n.title like ?) or m.type=2) and m.shenhe=? ORDER BY m.id DESC";
			public static final String SEARCH_ALL ="from Comment m where m.newsId in(select n.id from News n where n.title like ?) or m.type=2 ORDER BY m.id DESC";
			public static final String GET_COUNT_SEARCH_ALL =  "select count(*) from Comment m where m.newsId in(select n.id from News n where n.title like ?) or m.type=2 ORDER BY m.id DESC";
			public static final String GET_COUNT_BY_NEWS_CONT = "SELECT COUNT(*) FROM Comment m where m.newsId=? and m.cont=?";
		}
   
		public static final class Ad {
			public static final String LIST = "from Ad m ORDER BY m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Ad m";
			public static final String LIST_ON = "from Ad m where m.status=1 ORDER BY m.id DESC";
			public static final String GET_COUNT_ON = "SELECT COUNT(*) FROM Ad m where m.status=1";
		}

		public static final class FriendsLink {
			public static final String LIST = "from FriendsLink m ORDER BY m.idx";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM FriendsLink m";
			public static final String LIST_UP = "FROM FriendsLink m WHERE m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM FriendsLink m WHERE m.idx>? order by m.idx asc";
			public static final String GET_MAX_IDX = "select max(idx)+1 from FriendsLink m";
		}

		public static final class Lanmu {
			public static final String LIST = "from Lanmu m where m.pindaoId=? ORDER BY m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Lanmu m where m.pindaoId=? ";
		}

		public static final class CityCode {
			public static final String LIST = "from CityCode m ORDER BY m.id DESC";
			public static final String FIND = "from CityCode m where m.name=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM CityCode m";
		}

		public static final class Pindao {
			public static final String GET_MAX_IDX = "select max(idx)+1 from Pindao m where m.fatherId=?";
			public static final String LIST = "from Pindao m where m.fatherId=0 ORDER BY m.idx";
			public static final String LIST_SHOW = "from Pindao m where m.fatherId=0 and shows=true ORDER BY m.idx";
			public static final String LIST_SUB = "from Pindao m where m.fatherId=? ORDER BY m.idx ";
			public static final String LIST_SUB_SHOW = "from Pindao m where m.fatherId=? and shows=true ORDER BY m.idx ";
			public static final String LIST_UP = "FROM Pindao m WHERE m.fatherId=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM Pindao m WHERE m.fatherId=? and m.idx>? order by m.idx asc";
		}

		public static final class RemotePindao {
			public static final String GET_MAX_IDX = "select max(idx)+1 from RemotePindao m where m.fatherId=?";
			public static final String LIST = "from RemotePindao m where m.fatherId=0 ORDER BY m.idx";
			public static final String LIST_SHOW = "from RemotePindao m where m.fatherId=0 and shows=true ORDER BY m.idx";
			public static final String LIST_SUB = "from RemotePindao m where m.fatherId=? ORDER BY m.idx ";
			public static final String LIST_SUB_SHOW = "from RemotePindao m where m.fatherId=? and shows=true ORDER BY m.idx ";
			public static final String LIST_UP = "FROM RemotePindao m WHERE m.fatherId=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM RemotePindao m WHERE m.fatherId=? and m.idx>? order by m.idx asc";
			public static final String LIST_BY_PINDAOID = "from RemotePindao m where m.id in(select remoteId from PindaoRule n where n.pindaoId=?) ORDER BY m.idx";
		}

		public static final class News {
			public static final String LIST_BY_ZHUANTI = "from News m where m.id in(select n.newsId from ZhuantiNews n where n.zhuantiId=?) order by m.id desc";
			public static final String Get_Prev = "from News m where m.id<? and m.pindaoId=? and m.status=1 order by m.id desc";
			public static final String Get_Next = "from News m where m.id>? and m.pindaoId=? and m.status=1 order by m.id asc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM News m where m.id in(select n.newsId from ZhuantiNews n where n.zhuantiId=?)";
			public static final String GET_PINDAO_COUNT = "SELECT COUNT(*) FROM News m where (m.pindaoId=? or m.pindaoId in(select p.id from Pindao p where p.fatherId=?)) and m.status=1";
		}

		public static final class Qudao {
			public static final String LIST = "from Qudao m ORDER BY m.id DESC";
			public static final String find_by_code = "from Qudao m where m.code=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Qudao m";
		}

		public static final class NewsImage {
			public static final String LIST = "from NewsImage m where m.newsId=? ORDER BY m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM NewsImage m where m.newsId=? ";
			public static final String LIST_ALL = "from NewsImage m where m.newsId in(?,?) ORDER BY m.idx";
			public static final String LIST_UP = "FROM NewsImage m WHERE m.newsId=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM NewsImage m WHERE m.newsId=? and m.idx>? order by m.idx asc";
			public static final String GET_MAX_IDX = "select max(idx)+1 from NewsImage m where m.newsId=?";
		}

		public static final class PageBlock {
			public static final String LIST = "from PageBlock m ORDER BY m.blockName,m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM PageBlock m ";
		}

		public static final class PageBlockContent {
			public static final String LIST_CONTENT = "from PageBlockContent m where m.pageBlockId=?  ORDER BY m.idx";
			public static final String getTop = "from PageBlockContent m where m.pageBlockId=?  ORDER BY m.idx";
			public static final String LIST_CONTENT_PINDAO = "from PageBlockContent m where m.pageBlockId=? and (m.pindaoId in(select p.id from Pindao p where p.fatherId=? or p.id=?)) ORDER BY m.idx";
			public static final String LIST_BLOCK = "from PageBlockContent m where m.contId=? and m.type=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM PageBlockContent m where m.pageBlockId=?";
			public static final String DELETE_BY_contID = "DELETE FROM PageBlockContent m WHERE m.contId=? and m.type=?";
		}

		public static final class ViewLog {
			public static final String LIST = "from ViewLog m ORDER BY m.id DESC";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM ViewLog m";
			public static final String get_pv = "SELECT COUNT(*) FROM ViewLog m where m.day=?";
			public static final String get_ip= "SELECT count(distinct m.ip) FROM ViewLog m where m.day=?";
			public static final String get_uv = "SELECT count(distinct m.mobile) FROM ViewLog m where m.day=?";
			public static final String get_hhpv = "SELECT COUNT(*) FROM ViewLog m where m.day=? and m.hour=?";
			public static final String get_hhip = "SELECT count(distinct m.ip) FROM ViewLog m where m.day=? and m.hour=?";
			public static final String get_hhuv = "SELECT count(distinct m.mobile) FROM ViewLog m where m.day=? and m.hour=?";
		}

		public static final class Zhuanti {
			public static final String GET_MAX_IDX = "select max(idx)+1 from Zhuanti m where m.fatherId=?";
			public static final String LIST = "from Zhuanti m where m.fatherId=0 ORDER BY m.idx";
			public static final String LIST_SUB = "from Zhuanti m where m.fatherId=? ORDER BY m.idx ";
			public static final String LIST_UP = "FROM Zhuanti m WHERE m.fatherId=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM Zhuanti m WHERE m.fatherId=? and m.idx>? order by m.idx asc";
		}

		public static final class ZhuantiNews {
			public static final String LIST = "from ZhuantiNews m where m.zhuantiId=? order by m.idx";
			//public static final String LIST_CONTENT = "from PageBlockContent m where m.pageBlockId=?  ORDER BY m.idx";
			public static final String FIND_NEWS = "from ZhuantiNews m where m.zhuantiId=? and m.newsId=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM ZhuantiNews m where m.zhuantiId=?";
			public static final String GET_COUNT_BY_BLOCK = "SELECT COUNT(*) FROM ZhuantiNews m where m.blockId=?";
			public static final String LISTBYBLOCK = "from ZhuantiNews m where m.blockId=? order by m.idx";
		}

		public static final class Feedback {

			public static final String LIST = "from Feedback m order by id desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Feedback m";

		}

		public static final class PindaoRule {

			public static final String LIST = "from PindaoRule m where m.pindaoId=?";
			public static final String FIND = "from PindaoRule m where m.pindaoId=? and m.remoteId=?";
			public static final String SELECTED = "from PindaoRule m where m.pindaoId!=? and m.remoteId=?";
			public static final String DELETE_BY_PindaoID = "DELETE FROM PindaoRule m WHERE m.pindaoId=?";

		}

		public static final class Badwords {

			public static final String LIST = "from Badwords m order by m.id desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Badwords m";

		}

		public static final class Block {

			public static final String LIST = "from Block m order by m.id desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Block m";
			public static final String LIST_BY_TYPE = "from Block m where m.type=? and containerId=? order by m.idx";
			public static final String LIST_UP = "FROM Block m WHERE m.containerId=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM Block m WHERE m.containerId=? and m.idx>? order by m.idx asc";
		}
		
		public static final class PageContent {

			public static final String LIST = "from PageContent m order by m.id desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM PageContent m";
			public static final String LIST_BY_PAGE = "from PageContent m where m.page=? order by m.idx";
			public static final String LIST_UP = "FROM PageContent m WHERE m.page=? and m.idx<? order by m.idx desc";
			public static final String LIST_DOWN = "FROM PageContent m WHERE m.page=? and m.idx>? order by m.idx asc";
		}

		public static final class IpLocation {
			public static final String LIST = "from IpLocation m order by m.id desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM IpLocation m";
			public static final String FIND_BY_IP = "from IpLocation m where m.ipvalue<=? and ip2value>=?";
			public static final String LIST_NULL_LOCAL = "from IpLocation m where m.province is null order by m.id";
		}

		public static final class Tongji{

			public static final String LIST = "from Tongji m where m.type=? and m.date>=? and m.date<? order by m.date desc";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Tongji m where m.type=? and m.date>=? and m.date<?";
			public static final String FIND = "from Tongji m where m.type=? and m.date=?";
			public static final String LIST_ORDER_BY_PV = "from Tongji m where m.type=? and m.date>=? and m.date<? order by m.pv desc";
			
		}

		public static final class SysParam {

			public static final String FIND="from SysParam m where m.name=?";
			
		}


	}

	public static final class SQL {

		public static final class MYSQL {
			public static final class PAGINATION {
				public static String SQL(String sql, int startRows,
						int pagePerSize) {
					return "select * from (" + sql + ") ttt order by c1 desc limit " + startRows
							+ "," + pagePerSize;
				}
			}

			public static final class TONGJI {
				
				
				public static final String TONGJI_BY_DAY = "select a.time c1,a.pv c2,c.uv c3,b.ipcount c4 from "
						+ " (select v.day time,count(*) pv from ViewLog v where v.day=?) a "
						+ " left join (select v.day time,count(distinct v.ip) ipcount from ViewLog v where v.day=?) b on b.time=a.time "
						+ " left join (select v.day time,count(distinct v.mobile) uv from ViewLog v where v.day=?) c on c.time=a.time ";

				public static final String getcountbyday = "select count(*) from (select v.day time from ViewLog v where v.savetime>=? and v.savetime<? group by v.day)) m"; 

				public static final String TONGJI_BY_PINDAO = "select v.pindaoId,count(*) c from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') and v.pindaoId>0 group by v.pindaoId order by c desc";
				
				public static final String TONGJI_BY_Version = "select ifnull(v.v,'δ֪'),count(*) c from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.v order by c desc";
				
				public static final String TONGJI_PAGE = "select v.actionid,count(*) pv from ViewLog v where v.pindaoId=? and v.savetime>=? and v.savetime<? and v.actionid!=3 group by v.actionid";
				
				public static final String TONGJI_PAGE2 = "select v.name,ifnull(a.pv,0) from Pindao v left join (select v.lanmuId,count(*) pv from ViewLog v where v.pindaoId=? and v.savetime>=? and v.savetime<? and v.actionid=3 group by v.lanmuId) a on a.lanmuId=v.id where v.fatherId=?";

				public static final String TONGJI_Area = "select v.province,count(*) pv from ViewLog v where v.savetime>=? and v.savetime<? and v.province is not null group by v.province order by pv desc";

				public static final String TONGJI_Qudao = "select v.qudaoId,count(*) pv from ViewLog v where v.savetime>=? and v.savetime<? group by v.qudaoId order by pv desc";

				public static final String TONGJI_Ad = "select ifnull(v.type1,'0'),ifnull(v.newsId,0),count(*) c from ViewLog v where v.savetime>=? and v.savetime<? and actionid=18 group by type1,newsId order by c desc";

				public static final String TONGJI_BY_HOUR ="select a.time c1,a.pv c2,c.uv c3,b.ipcount c4 from "
					+ " (select v.hour time,count(*) pv from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) a "
					+ " left join (select v.hour time,count(distinct v.ip) ipcount from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) b on b.time=a.time "
					+ " left join (select v.hour time,count(distinct v.mobile) uv from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) c on c.time=a.time order by a.time";
			}  
		}
	}
}